package excelrept

import (
	"fmt"
	"strconv"
	"strings"

	"github.com/xuri/excelize/v2"
	"shqsoft.cn/pas3ui/base"
)

var tplPool map[string]*Template = make(map[string]*Template)

//Range 范围
type Range struct {
	SCol int `json:"scol"`
	ECol int `json:"ecol"`
	SRow int `json:"srow"`
	ERow int `json:"erow"`
}

//Template 模板
type Template struct {
	Sheets        []Sheet           `json:"sheets"`
	Colors        map[string]string `json:"colors"`
	WidthrRatio   float64           `json:"widthrRatio"`
	HeightRatio   float64           `json:"heightRatio"`
	RowHeadTopOff float64           `json:"rowHeadTopOff"`
	Remarks       string            `json:"remarks"`
	Params        []Parameter       `json:"params"`
}

//Sheet 表单
type Sheet struct {
	Width        float64              `json:"width"`
	SheetName    string               `json:"sheetName"`
	MergeCells   map[string]MergeCell `json:"mergeCells"`
	ColumnsWidth map[int]float64      `json:"columnsWidth"`
	TableItems   []TableItem          `json:"tableItems"`
}

//TableItem 表
type TableItem struct {
	TableName        string            `json:"tableName"`
	Title            string            `json:"title"`
	RowHeights       map[int]float64   `json:"rowHeights"`
	DataCells        map[string]string `json:"dataCells"`
	HeadCells        map[string]string `json:"headCells"`
	TailCells        map[string]string `json:"tailCells"`
	ColumnCells      map[string]string `json:"columnCells"`
	ColumnTypes      map[int]string    `json:"columnTypes"`
	RowNoCol         string            `json:"rowNoCol"`
	HeadRange        Range             `json:"headRange"`
	ColumnRange      Range             `json:"columnRange"`
	DataRange        Range             `json:"dataRange"`
	TailRange        Range             `json:"tailRange"`
	FixedColumnRange Range             `json:"fixedColumnRange"`
	RowRange         Range             `json:"rowRange"`
	FormulaCells     []Range           `json:"formulaCells"`
	UnusedCells      []Range           `json:"unusedCells"`
	cellformulas     map[string]string
}

func (t *Template) getTableItem(tName string) *TableItem {
	if tName == "" {
		if len(t.Sheets) > 0 && len(t.Sheets[0].TableItems) > 0 {
			item := t.Sheets[0].TableItems[0]
			return &item
		}
	}
	for i := 0; i < len(t.Sheets); i++ {
		for j := 0; j < len(t.Sheets[i].TableItems); j++ {
			if t.Sheets[i].TableItems[j].TableName == tName {
				return &(t.Sheets[i].TableItems[j])
			}
		}
	}
	return nil
}
func (t *TableItem) parseExp(exp string) (cells []string, minus []int, err error) {
	ebs := []byte(exp)
	var bi int
	bz := 'F' //标记，F:开始，A:字母或者]，N:数字或者点,D:数字或者],M:字母或数字,B:[
	cells = make([]string, 0, 5)
	minus = make([]int, 0, 5)
	var cellbs []byte
	index := 0
	var state int
	colStart := true
	for i, b := range ebs {
		if b == ' ' {
			continue
		}
		bi = int(b)
		if bz == 'F' {
			if b == '[' {
				cellbs = make([]byte, 0, 5)
				bz = 'M'
				state = 0
				continue
			} else if bi == 43 || bi == 45 {
				if bi == 45 {
					minus = append(minus, index)
				}
				bz = 'B'
			} else {
				err = fmt.Errorf("错误的计算表达式%s，表达式的第%d位必须是[或者+，-符号", exp, i)
			}
		} else if bz == 'B' {
			if b == '[' {
				cellbs = make([]byte, 0, 5)
				bz = 'M'
				continue
			} else {
				err = fmt.Errorf("错误的计算表达式%s，表达式的第%d位是[符号", exp, i)
				return
			}
		} else if bz == 'M' {
			if bi >= 65 && bi <= 90 {
				cellbs = append(cellbs, b)
				if state == 0 {
					colStart = true
					state++
				} else {
					if !colStart {
						state++
					}
				}
				if state == 1 {
					bz = 'M'
				} else {
					bz = 'A'
				}
			} else {
				err = fmt.Errorf("错误的计算表达式%s，表达式的第%d位应该是大写字母或者数字，但不能为0", exp, i)
				return
			}
		} else if bz == 'A' {
			if bi >= 65 && bi <= 90 {
				bz = 'A'
				cellbs = append(cellbs, b)
			} else if b == ']' {
				bz = 'F'
				continue
			} else {
				err = fmt.Errorf("错误的计算表达式%s，表达式的第%d位应该是大写字母", exp, i)
				return
			}
		} else if bz == 'N' {
			if bi >= 48 && bi <= 57 {
				cellbs = append(cellbs, b)
				if state == 0 {
					colStart = false
					state++
				} else {
					if colStart {
						state++
					}
				}
				if state == 1 {
					bz = 'N'
				} else {
					bz = 'D'
				}
			} else if b == '.' {
				cellbs = append(cellbs, b)
				bz = 'M'
			} else {
				err = fmt.Errorf("错误的计算表达式%s，表达式的第%d位应该是数字.符号", exp, i)
				return
			}
		}
	}
	if bz != 'N' {
		err = fmt.Errorf("错误的计算表达式%s，表达式不完整", exp)
	}
	cells = append(cells, string(cellbs))
	return
}

//GetTemplate 获取模板
func GetTemplate(reptName string) (tpl *Template, err error) {
	// tpl, ok := tplPool[reptName]
	// if ok {
	// 	return
	// }
	tpl, err = loadTemplate(reptName)
	if err != nil {
		return nil, err
	}
	// tplPool[reptName] = tpl
	return
}

func loadTemplate(reptName string) (tpl *Template, err error) {
	wdir, err := base.WorkDir()
	if err != nil {
		return nil, err
	}
	path := wdir + "/template/" + reptName + "-PAGE.xlsx"
	isok, err := base.PathExists(path)
	if err != nil {
		return nil, err
	}
	if !isok {
		path = wdir + "/template/" + reptName + ".xlsx"
	}
	xlsx, err := NewXlsxHelper(path)
	if err != nil {
		return nil, err
	}
	conf, err := getConfig(reptName)
	if err != nil {
		return nil, err
	}

	tlen := len(conf.Tables)
	if tlen == 0 {
		return nil, fmt.Errorf("报表%s没有配置tables", reptName)
	}
	var tItem TableItem
	tpl = &Template{
		Sheets:  make([]Sheet, 0, 3),
		Colors:  conf.Colors,
		Remarks: conf.Remarks,
		Params:  conf.Params,
	}
	tpl.HeightRatio = 1.43
	if conf.HeightRatio > 0 {
		tpl.HeightRatio = conf.HeightRatio
	}
	tpl.WidthrRatio = 1.22
	if conf.WidthrRatio > 0 {
		tpl.WidthrRatio = conf.WidthrRatio
	}
	tpl.RowHeadTopOff = conf.RowHeadTopOff
	var hs1, hs2, hs3, hs4 map[int]float64
	var scol, ecol, srow, erow int
	for _, t := range conf.Tables {
		tItem.TableName = t.Name
		tItem.Title = t.Title
		for k, v := range t.Columns {
			if t.RowNoCol == v {
				tItem.RowNoCol = k
				break
			}
		}
		scol, ecol, srow, erow, err = parseRange(t.HeadRange)
		if err != nil {
			return
		}
		tItem.HeadRange = Range{SCol: scol, SRow: srow, ECol: ecol, ERow: erow}
		scol, ecol, srow, erow, err = parseRange(t.ColumnRange)
		if err != nil {
			return
		}
		tItem.ColumnRange = Range{SCol: scol, SRow: srow, ECol: ecol, ERow: erow}
		fixedColumnRange := Range{}
		if t.ColStart > 1 {
			fixedColumnRange.SCol = scol
			fixedColumnRange.SRow = srow
			fixedColumnRange.ERow = erow
		}
		scol, ecol, srow, erow, err = parseRange(t.DataRange)
		if err != nil {
			return
		}
		tItem.DataRange = Range{SCol: scol, SRow: srow, ECol: ecol, ERow: erow}
		rowRange := Range{}
		if t.ColStart > 1 {
			rowRange.SRow = srow
			rowRange.ERow = erow
			rowRange.SCol = scol
			rowRange.ECol = t.ColStart - 1
			fixedColumnRange.ECol = t.ColStart - 1
		}
		tItem.FixedColumnRange = fixedColumnRange
		tItem.RowRange = rowRange
		scol, ecol, srow, erow, err = parseRange(t.TailRange)
		if err != nil {
			return
		}
		tItem.TailRange = Range{SCol: scol, SRow: srow, ECol: ecol, ERow: erow}
		tItem.DataCells, hs1, err = xlsx.ReaderCells(t.Sheet, t.DataRange)
		if err != nil {
			return
		}
		tItem.HeadCells, hs2, err = xlsx.ReaderCells(t.Sheet, t.HeadRange)
		if err != nil {
			return
		}
		tItem.TailCells, hs3, err = xlsx.ReaderCells(t.Sheet, t.TailRange)
		if err != nil {
			return
		}
		tItem.ColumnCells, hs4, err = xlsx.ReaderCells(t.Sheet, t.ColumnRange)
		if err != nil {
			return
		}
		for k, v := range hs1 {
			hs1[k] = v * 0.3612 / 0.3527
		}
		for k, v := range hs2 {
			hs1[k] = v * 0.3612 / 0.3527
		}
		for k, v := range hs3 {
			hs1[k] = v * 0.3612 / 0.3527
		}
		for k, v := range hs4 {
			hs1[k] = v * 0.3612 / 0.3527
		}
		tItem.RowHeights = hs1
		sheet, err := getSheet(tpl, conf, xlsx, t.Sheet)
		if err != nil {
			return nil, err
		}
		tItem.FormulaCells, err = parseRanges(t.FormulaCells)
		if err != nil {
			return nil, err
		}
		tItem.UnusedCells, err = parseRanges(t.UnusedCells)
		if err != nil {
			return nil, err
		}
		dbCols, err := getDBColumns(t.Name)
		if err != nil {
			return nil, err
		}
		colTypes := make(map[int]string)
		var index int
		if len(t.Columns) > 0 {
			for k, v := range t.Columns {
				index, err = excelize.ColumnNameToNumber(k)
				if err != nil {
					return nil, err
				}
				for _, dbcol := range dbCols {
					if strings.ToUpper(v) == strings.ToUpper(dbcol.Name) {
						switch dbcol.DataType {
						case "int":
							colTypes[index] = "integer"
						case "money", "decimal", "float", "numeric":
							colTypes[index] = "number"
						case "date", "datetime":
							colTypes[index] = "date"
						default:
							colTypes[index] = "string"
						}
					}
				}
			}
		} else {
			if len(t.Cells) == 0 {
				return nil, fmt.Errorf("报表%s没有配置columns或者cells", reptName)
			}
			for i := tItem.DataRange.SCol; i <= tItem.DataRange.ECol; i++ {
				colTypes[index] = "number"
			}
		}
		tItem.ColumnTypes = colTypes
		tItem.cellformulas, err = loadCellformulas(xlsx, t.Sheet, tItem.FormulaCells)
		if err != nil {
			return nil, err
		}
		sheet.TableItems = append(sheet.TableItems, tItem)
	}
	return
}
func getSheet(tpl *Template, conf *ReptConf, xlsx *XlsxHelper, sheetName string) (sheet *Sheet, err error) {
	if tpl.Sheets != nil {
		for i := 0; i < len(tpl.Sheets); i++ {
			if tpl.Sheets[i].SheetName == sheetName {
				return &(tpl.Sheets[i]), nil
			}
		}
	} else {
		tpl.Sheets = make([]Sheet, 0, 3)
	}
	maxCol := 0
	for _, t := range conf.Tables {
		if t.Sheet == sheetName {
			rs := strings.Split(t.DataRange, ":")
			if len(rs) != 2 {
				return nil, fmt.Errorf("无效的DataRange表达式%s", t.DataRange)
			}
			col, _, err := parseAxis(rs[1])
			if err != nil {
				return nil, err
			}
			if col > maxCol {
				maxCol = col
			}
		}
	}
	colStr, err := excelize.ColumnNumberToName(maxCol)
	if err != nil {
		return nil, err
	}
	cws, w, err := xlsx.GetCloumnWidths(sheetName, "A", colStr)
	if err != nil {
		return nil, err
	}
	s := Sheet{
		SheetName:    sheetName,
		Width:        w,
		ColumnsWidth: cws,
		MergeCells:   xlsx.GetMergeCells(sheetName),
		TableItems:   make([]TableItem, 0, 3),
	}
	tpl.Sheets = append(tpl.Sheets, s)
	sheet = &(tpl.Sheets[len(tpl.Sheets)-1])
	return
}

func loadCellformulas(xlsx *XlsxHelper, sheetName string, formulaRange []Range) (cellformulas map[string]string, err error) {
	cellformulas = make(map[string]string, 20)
	var axis string
	var cName string
	for _, rg := range formulaRange {
		for j := rg.SCol; j <= rg.ECol; j++ {
			cName, err = excelize.ColumnNumberToName(j)
			if err != nil {
				return
			}
			for i := rg.SRow; i <= rg.ERow; i++ {
				axis = cName + strconv.Itoa(i)
				cellformulas[axis], err = xlsx.file.GetCellFormula(sheetName, axis)
				if err != nil {
					return
				}
			}
		}
	}
	return
}

//ReptInfo 报表信息
type ReptInfo struct {
	TBJG string
	TBSJ string
	LXDH string
	TBR  string
	FHR  string
	SPR  string
}
